package com.cse.p2p.database;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
import java.util.Map;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;

/**
 *
 * @author DUCMINHKHOI
 */
public class ClientDatabase {
	
	private static final String DATABASE_URL = "jdbc:sqlite:client.db";
	private static boolean isDatabaseOpen = false;
	private static Connection connection = null;
	private static Statement statement = null;
	private static ResultSet resultSet = null;

	/**
	 * This method will open the Database client.db
	 * consist of open connection, statement
	 * This method is called only once at the beginning of the client's life
	 * @throws SQLException
	 * @throws Exception
	 *
	 */
	public static void openDatabase() throws SQLException, Exception {
		if ( !isDatabaseOpen ) {
			Class.forName( "org.sqlite.JDBC" );
			connection = DriverManager.getConnection( DATABASE_URL );
			statement = connection.createStatement();
			statement.execute( "CREATE TABLE if not exists file("
							   + "fileid integer primary key,"
							   + "filename varchar(255) not null,"
							   + "filerealname varchar(255) not null unique,"
							   + "filesize bigint not null,"
							   + "filepath varchar(255) not null,"
							   + "filedescribe varchar(255),"
							   + "clientupip varchar(15) not null);commit;" );
			statement.execute( "CREATE TABLE if not exists piece("
							   + "pieceid integer,"
							   + "fileid integer,"
							   + "hashkey varchar(100) not null,"
							   + "primary key(pieceid, fileid)); commit;" );
			isDatabaseOpen = true;
		}
	}

	/**
	 * This method with close the Database
	 * This method is called once at the end of the client's life
	 */
	public static void closeDatabase() {
		try {
			connection.close();
		}
		catch ( Exception e ) {
		}
		
		isDatabaseOpen = false;
	}
	
	private static void closeResultSetAndStatment() {
		try {
			resultSet.close();
			statement.close();
		}
		catch ( Exception e ) {
		}
	}

	/**
	 * This method will insert new row in File table
	 * Using when read the DicFile (receiving from Server) the first time
	 * @param fileName: name of the file
	 * @param fileRealName: the real name of the file
	 * @param fileID: the file's ID that written in the Server's database
	 * @param fileSize: the file's Size
	 * @param filePath: the file's Path
	 * @param fileDescribe: the file's Describe
	 * @param clientUpIP: the IP of the client upload this file to the server
	 * @throws SQLException
	 */
	public static void NewFile( String fileName, String fileRealName, int fileID,
								long fileSize, String filePath, String fileDescribe,
								String clientUpIP ) throws SQLException, Exception {
		openDatabase();
		statement.executeUpdate( String.format( "insert into file values(%d, '%s', '%s', %d, '%s', '%s', '%s');",
												fileID, fileName, fileRealName, fileSize, filePath, fileDescribe, clientUpIP ) );
		closeResultSetAndStatment();
		closeDatabase();
	}

	/**
	 * This method will insert new row in Piece table
	 * Using when complete receiving a piece d will insert new row in Piece tablefrom other client
	 * @param pieceID: the number order of the piece in the whole file
	 * @param fileID: the file's ID that written in Server's database
	 * @param hashkey: the hashkey (must in String type)
	 * @throws SQLException
	 */
	public static void NewPiece( int pieceID, int fileID, String hashkey ) throws SQLException, Exception {
		openDatabase();
		statement.executeUpdate( String.format( "insert into piece values(%d, %d, '%s');",
												pieceID, fileID, hashkey ) );
		
		closeResultSetAndStatment();
		closeDatabase();
	}

	/**
	 * This method will check whether a piece exists in Piece table
	 * Using when Other client request for status of file
	 * OR when this client have this piece of the whole file and don't want downloading this piece again
	 * @param pieceID: the number order of the piece in the whole file
	 * @param fileID: the file's ID that written in Server's database
	 * @return true OR false: this piece in the Piece table or not
	 * @throws SQLException
	 */
	public static boolean checkPieceExist( int pieceID, int fileID ) throws SQLException, Exception {
		openDatabase();
		boolean checkExist = true;
		
		resultSet = statement.executeQuery( "select * from piece where pieceid = " + pieceID
											+ " and fileid = " + fileID + ";" );
		if ( !resultSet.next() ) {
			checkExist = false;
		}
		closeResultSetAndStatment();
		closeDatabase();
		return checkExist;
	}

	/**
	 * This method will get the existing Piece related to given fileID in the piece table
	 * Using when Server want to get list of piece ID from client's database
	 * @param fileID: file's ID
	 * @return ListOfPieceExist: type LinkedList
	 * @throws SQLException
	 */
	public static List getPiecesExist( int fileID ) throws SQLException, Exception {
		openDatabase();
		List pieceIDList = new LinkedList();
		
		resultSet = statement.executeQuery( "select pieceid from piece where fileid = " + fileID + ";" );
		while ( resultSet.next() ) {
			pieceIDList.add( resultSet.getInt( "pieceid" ) );
		}
		closeResultSetAndStatment();
		closeDatabase();
		return pieceIDList;
	}

	/**
	 * This method will return the path of a given file's ID from file table
	 * @param fileID: file's ID
	 * @return filePath: String of filePath
	 * @throws SQLException
	 */
	public static String getFilePath( int fileID ) throws SQLException, Exception {
		openDatabase();
		String filePath = null;
		resultSet = statement.executeQuery( "select * from file where fileid = " + fileID + ";" );
		if ( resultSet.next() ) {
			filePath = resultSet.getString( "filepath" );
		}
		closeResultSetAndStatment();
		closeDatabase();
		return filePath;
	}

	/**
	 * This method will return the fileName of given file's ID from file table
	 * @param fileID: file's ID
	 * @return fileName: String of fileName
	 * @throws SQLException
	 */
	public static String getFileName( int fileID ) throws SQLException, Exception {
		openDatabase();
		String fileName = null;
		resultSet = statement.executeQuery( "select * from file where fileid = " + fileID + ";" );
		if ( resultSet.next() ) {
			fileName = resultSet.getString( "filename" );
		}
		closeResultSetAndStatment();
		closeDatabase();
		return fileName;
	}

	/**
	 * This method will return the fileSize of given file's ID from file table
	 * @param fileID: file's ID
	 * @return fileSize: Long type of fileSize
	 * @throws SQLException
	 */
	public static long getFileSize( int fileID ) throws SQLException, Exception {
		openDatabase();
		long fileSize = 0;
		resultSet = statement.executeQuery( "select * from file where fileid = " + fileID + ";" );
		if ( resultSet.next() ) {
			fileSize = resultSet.getLong( "filesize" );
		}
		closeResultSetAndStatment();
		closeDatabase();
		return fileSize;
	}

	/**
	 * This method will return the fileDescribe of given file's ID from the file table
	 * @param fileID: file's ID
	 * @return fileDescribe: String of describe
	 * @throws SQLException
	 */
	public static String getFileDescribe( int fileID ) throws SQLException, Exception {
		openDatabase();
		String fileDescribe = null;
		resultSet = statement.executeQuery( "select * from file where fileid = " + fileID + ";" );
		if ( resultSet.next() ) {
			fileDescribe = resultSet.getString( "filedescribe" );
		}
		closeResultSetAndStatment();
		closeDatabase();
		return fileDescribe;
	}

	/**
	 * This method will return the fileRealName of a given file's ID from file table
	 * @param fileID: file's ID
	 * @return fileRealName: String of file real name
	 * @throws SQLException
	 */
	public static String getFileRealName( int fileID ) throws SQLException, Exception {
		openDatabase();
		String fileRealName = null;
		resultSet = statement.executeQuery( "select * from file where fileid = " + fileID + ";" );
		if ( resultSet.next() ) {
			fileRealName = resultSet.getString( "filerealname" );
		}
		closeResultSetAndStatment();
		closeDatabase();
		return fileRealName;
	}

	/**
	 * This method will return the ClientUPIP of a given file's ID from file table
	 * @param fileID: file's ID
	 * @return clientUpIP: String of IP of client upload this file to Server
	 * @throws SQLException
	 */
	public static String getClientUpIP( int fileID ) throws SQLException, Exception {
		openDatabase();
		String clientUpIP = null;
		resultSet = statement.executeQuery( "select * from file where fileid = " + fileID + ";" );
		if ( resultSet.next() ) {
			clientUpIP = resultSet.getString( "clientupip" );
		}
		closeResultSetAndStatment();
		closeDatabase();
		return clientUpIP;
	}

	/**
	 * This method will return the HashCode of given pieceID and fileID from the piece table
	 * @param pieceID: the other number of this Piece in the whole file
	 * @param fileID: the file's ID
	 * @return hashcode: String of hashCode
	 * @throws SQLException
	 */
	public static String getPieceHashCode( int pieceID, int fileID ) throws SQLException, Exception {
		openDatabase();
		String hashCode = null;
		resultSet = statement.executeQuery( "select * from piece where pieceid = " + pieceID
											+ " and fileid = " + fileID + ";" );
		if ( resultSet.next() ) {
			hashCode = resultSet.getString( "hashkey" );
		}
		closeResultSetAndStatment();
		closeDatabase();
		return hashCode;
	}
	
	public static void removeFile( int fileID ) {
		try {
			System.out.println( "DATABASE: Entering the deletion routine with file id " + fileID );
			openDatabase();
			statement.executeUpdate( "delete from piece where fileid = " + fileID + ";" );
			statement.executeUpdate( "delete from file where fileid = " + fileID + ";" );
			closeResultSetAndStatment();
			closeDatabase();
		}
		catch ( Exception e ) {
			System.err.println( "DATABASE: ERROR: error when removing record" );
			e.printStackTrace();
		}
	}
}
